﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace UdrinkWater_Project.Admin
{
    public partial class OrderMaterial : System.Web.UI.Page
    {
        string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        DateTime OrderM_ID = DateTime.Now;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string T1 = OrderM_ID.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH"));
                string T2 = OrderM_ID.ToString("HH:mm:ss", new System.Globalization.CultureInfo("en-US"));
                txtOrderDate.Text = T1 + " " + T2;
                chkRow.Value = "0";
                Session["dtTemp"] = null;
            }
            else
            {

            }
        }


        protected void ASPxGridView4_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            DataTable DataPro = new DataTable();
            if (e.CommandArgs.CommandName == "Cmd_Select")
            {
                string Id = e.CommandArgs.CommandArgument.ToString();
                DataPro = GetDetialPro(Id);
                txtProductId.Text = DataPro.Rows[0]["Material_ID"].ToString();
                txtProductName.Text = DataPro.Rows[0]["Materail_name"].ToString();
                txtInStock.Text = DataPro.Rows[0]["TotalSum"].ToString();
                txtUnit.Text = DataPro.Rows[0]["Unit_Name"].ToString();
                hdUnit.Value = DataPro.Rows[0]["Unit_ID"].ToString();
                float price = 0;
                txtUnitPrice.Text = price.ToString("0.00");
                txtCount.Text = "0";
                txtCount.Focus();
            }
        }

       
        
        protected void btAddList_Click(object sender, EventArgs e)
        {
            DataTable dtList = new DataTable();
            if (chkRow.Value == "0")
            {
                dtList.Columns.Add("OrderM_Date", typeof(DateTime));
                dtList.Columns.Add("Material_ID", typeof(string));
                dtList.Columns.Add("Material_Count", typeof(int));
                dtList.Columns.Add("Price", typeof(float));
                dtList.Columns.Add("Total", typeof(float));
                dtList.Columns.Add("Unit", typeof(string));
                chkRow.Value = "1";
                lbWranList.Visible = false;    
            }

            else if (chkRow.Value != "0")
            {
                dtList = (DataTable)Session["dtTemp"];
                for(int i = 0; i < dtList.Rows.Count ; i++)
                {
                    if (dtList.Rows[i]["Material_ID"].ToString() == txtProductId.Text)
                    {
                        lbWranList.Visible = true;
                        break;
                    }
                    else
                    lbWranList.Visible = false;
                    
                }
                
            }

            if(lbWranList.Visible == false)
            {
                    
                    DataRow dtRow = dtList.NewRow();
                    dtRow["OrderM_Date"] = Convert.ToDateTime(txtOrderDate.Text);
                    dtRow["Material_ID"] = txtProductId.Text;
                    dtRow["Material_Count"] = txtCount.Text;
                    dtRow["Price"] = txtUnitPrice.Text;
                    dtRow["Unit"] = hdUnit.Value;
                    dtRow["Total"] = Convert.ToDouble(txtCount.Text) * Convert.ToDouble(txtUnitPrice.Text);
                    dtList.Rows.Add(dtRow);
                    Session["dtTemp"] = dtList;
                    ASPxdtail.DataSource = dtList;
                    ASPxdtail.DataBind();
            }


            
        }

        protected void ASPxdtail_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            DataTable dtList = new DataTable();
            dtList = (DataTable)Session["dtTemp"];
            DataRow[] dtRow;
            string xxx = e.CommandArgs.CommandArgument.ToString();
            dtRow = dtList.Select("Material_ID = '"+xxx+"'");
            foreach (DataRow r in dtRow)
            {
                r.Delete();
            }
            Session["dtTemp"] = dtList;
            dtList.AcceptChanges();
            ASPxdtail.DataSource = dtList;
            ASPxdtail.DataBind();
            lbWranList.Visible = false;
        }

        protected void btSaveAndReport_Click(object sender, EventArgs e)
        {
            Session["OrderM_Date"] = null;
            DataTable dtList = new DataTable();
            dtList = (DataTable)Session["dtTemp"];
            if (Session["dtTemp"] != null)
            {
                if (dtList.Rows.Count > 0)//Session["dtTemp"] != null)
                {
                    dtList.Columns.Remove("Total");
                    dtList.Columns.Remove("Unit");
                    OrderMaterialDataContext db = new OrderMaterialDataContext();
                    TB_Order_Material x = new TB_Order_Material();
                    x.OrderM_Date = Convert.ToDateTime(txtOrderDate.Text);
                    Session["OrderM_Date"] = Convert.ToDateTime(txtOrderDate.Text);
                    x.Address = txtAdd.Text; 
                    x.Province_ID = int.Parse(dlPro.SelectedValue);
                    x.Aumphur_ID = int.Parse(dlAmp.SelectedValue);
                    x.District_ID = int.Parse(dlDis.SelectedValue);
                    x.M_Status_ID = 1;
                    db.TB_Order_Materials.InsertOnSubmit(x);
                    db.SubmitChanges();
                    SqlConnection destinationConnection = new SqlConnection(Connection);
                    destinationConnection.Open();
                    SqlBulkCopy bulkcopy = new SqlBulkCopy(Connection);
                    //I assume you have created the table previously
                    //Someone else here already showed how  
                    bulkcopy.DestinationTableName = "dbo.TB_OrderMaterial_list";
                    try
                    {
                        bulkcopy.WriteToServer(dtList);
                        Response.Write(@"<script language='javascript'>alert('ทำรายการเรียบร้อยแล้ว\n');</script>");
                        Response.Redirect("~/Admin/ReportOrderMaterial.aspx");
                    }
                    catch (Exception ex)
                    {

                    }
                }
                else
                {
                    Response.Write(@"<script language='javascript'>alert('ไม่ได้ทำรายการ\n');</script>");
                }
            }
            else
            {
                Response.Write(@"<script language='javascript'>alert('ไม่ได้ทำรายการ\n');</script>");
            }
        }

      

        private DataTable GetDetialPro(string Product_ID)
        {
            string sqltext = @"SELECT Inm.Material_ID , seUnit.Unit_ID,      seUnit.Materail_name,ISNULL( Inm.Total - OutM.SumTotal,0 )As TotalSum,seUnit.Unit_Name 
                                FROM ( select Material_ID ,ISNULL(SUM(Material_Count),0) AS Total FROM TB_Material_StockIN group by Material_ID) as Inm 
                                full outer join
	                                 ( select  [Material_ID],ISNULL (SUM([Material_Count]),0) as SumTotal FROM TB_Material_StockOUT group by Material_ID ) as OutM
	                            ON Inm.Material_ID = OutM.Material_ID
	                            inner join
	                            ( SELECT  Material_ID,Materail_name,Unit_Name,Unit_ID FROM TB_Material As Mat inner join  TB_Unit_Category as Unit on Mat.Unit = Unit.Unit_ID ) As seUnit
	                             ON Inm.Material_ID = seUnit.Material_ID
                                WHERE Inm.Material_ID ='" + Product_ID + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }

        protected void btNewOrder_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/Admin/OrderMaterial.aspx");
        }

        protected void ASPxGridView1_RowCommand(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewRowCommandEventArgs e)
        {
            OrderMaterialDataContext db = new OrderMaterialDataContext();
            string xOrderM_Date = e.CommandArgs.CommandArgument.ToString();
            var q = (from p in db.TB_Order_Materials
                    where p.OrderM_Date == Convert.ToDateTime(xOrderM_Date)
                    select p).First();
            txtOrderDate.Text = q.OrderM_Date.ToString();
           // txtCusAddress.Text = q.Address.ToString();
          //  dds.SelectedValue = q.M_Status_ID.ToString();
            Session["dtTemp"] = GetMDetial(q.OrderM_Date);
            ASPxdtail.DataSource = Session["dtTemp"];
            ASPxdtail.DataBind();
        }

        private DataTable GetMDetial(DateTime OrderM_Date)
        {
            string sOrderM_Date = OrderM_Date.ToString("yyyy-MM-dd HH:mm:ss",new System.Globalization.CultureInfo("en-US"));
            string sqltext = @"SELECT    OrderM_Date
                                        ,M.Material_ID
                                        ,Material_Count
                                        ,Price
                                        ,M.Unit
                                        ,Material_Count * Price As Total
                                        FROM TB_OrderMaterial_list 
                                        inner join TB_Material As M  on M.Material_ID = TB_OrderMaterial_list.Material_ID
                                        WHERE OrderM_Date ='" + sOrderM_Date + "' ";
            SqlConnection sqlconn = new SqlConnection(Connection);
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

    }
}